<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="em20941">SET TRANSACTION</title>
  <body>
    <p id="sql_command_desc">Sets the characteristics of the current transaction.</p>
    <section id="section2">
      <title>Synopsis</title>
      <codeblock id="sql_command_synopsis">SET TRANSACTION [<varname>transaction_mode</varname>] [READ ONLY | READ WRITE]

SET TRANSACTION SNAPSHOT <varname>snapshot_id</varname>

SET SESSION CHARACTERISTICS AS TRANSACTION <varname>transaction_mode</varname> 
     [READ ONLY | READ WRITE]
     [NOT] DEFERRABLE</codeblock>
      <p>where <varname>transaction_mode</varname> is one of:</p>
      <codeblock>   ISOLATION LEVEL {SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}</codeblock>
    </section>
    <section id="section3">
      <title>Description</title>
      <p>The <codeph>SET TRANSACTION</codeph> command sets the characteristics of the current
        transaction. It has no effect on any subsequent transactions.</p>
      <p>The available transaction characteristics are the transaction isolation level, the
        transaction access mode (read/write or read-only), and the deferrable mode. </p>
      <note>Deferrable transactions require the transaction to be serializable. Greenplum Database
        does not support serializable transactions, so including the <codeph>DEFERRABLE</codeph>
        clause has no effect.</note>
      <p>Greenplum Database does not support the <codeph>SET TRANSACTION SNAPSHOT</codeph> command. </p>
      <p>The isolation level of a transaction determines what data the transaction can see when
        other transactions are running concurrently.</p>
      <ul>
        <li id="em137940"><b>READ COMMITTED</b> — A statement can only see rows committed before it
          began. This is the default. </li>
        <li id="em137943"><b>REPEATABLE READ</b> — All statements in the current transaction can
          only see rows committed before the first query or data-modification statement run in
          the transaction.</li>
      </ul>
      <p>The SQL standard defines two additional levels, <codeph>READ UNCOMMITTED</codeph> and
          <codeph>SERIALIZABLE</codeph>. In Greenplum Database <codeph>READ UNCOMMITTED</codeph> is
        treated as <codeph>READ COMMITTED</codeph>. If you specify <codeph>SERIALIZABLE</codeph>,
        Greenplum Database falls back to <codeph>REPEATABLE READ</codeph>.</p>
      <p>The transaction isolation level cannot be changed after the first query or
        data-modification statement (<codeph>SELECT</codeph>, <codeph>INSERT</codeph>,
          <codeph>DELETE</codeph>, <codeph>UPDATE</codeph>, <codeph>FETCH</codeph>, or
          <codeph>COPY</codeph>) of a transaction has been run.</p>
      <p>The transaction access mode determines whether the transaction is read/write or read-only.
        Read/write is the default. When a transaction is read-only, the following SQL commands are
        disallowed: <codeph>INSERT</codeph>, <codeph>UPDATE</codeph>, <codeph>DELETE</codeph>, and
          <codeph>COPY FROM</codeph> if the table they would write to is not a temporary table; all
          <codeph>CREATE</codeph>, <codeph>ALTER</codeph>, and <codeph>DROP</codeph> commands;
          <codeph>GRANT</codeph>, <codeph>REVOKE</codeph>, <codeph>TRUNCATE</codeph>; and
          <codeph>EXPLAIN ANALYZE</codeph> and <codeph>EXECUTE</codeph> if the command they would
        run is among those listed. This is a high-level notion of read-only that does not
        prevent all writes to disk.</p>
      <p> The <codeph>DEFERRABLE</codeph> transaction property has no effect unless the transaction
        is also <codeph>SERIALIZABLE</codeph> and <codeph>READ ONLY</codeph>. When all of these
        properties are set on a transaction, the transaction may block when first acquiring its
        snapshot, after which it is able to run without the normal overhead of a
          <codeph>SERIALIZABLE</codeph> transaction and without any risk of contributing to or being
        cancelled by a serialization failure. Because Greenplum Database does not support
        serializable transactions, the <codeph>DEFERRABLE</codeph> transaction property has no
        effect in Greenplum Database. </p>
    </section>
    <section id="section4">
      <title>Parameters</title>
      <parml>
        <plentry>
          <pt>SESSION CHARACTERISTICS</pt>
          <pd>Sets the default transaction characteristics for subsequent transactions of a
            session.</pd>
        </plentry>
        <plentry>
          <pt>READ UNCOMMITTED</pt>
          <pt>READ COMMITTED</pt>
          <pt>REPEATABLE READ</pt>
          <pt>SERIALIZABLE</pt>
          <pd>The SQL standard defines four transaction isolation levels: <codeph>READ
              UNCOMMITTED</codeph>, <codeph>READ COMMITTED</codeph>, <codeph>REPEATABLE
              READ</codeph>, and <codeph>SERIALIZABLE</codeph>.</pd>
          <pd><codeph>READ UNCOMMITTED</codeph> allows transactions to see changes made by
            uncomitted concurrent transactions. This is not possible in Greenplum Database, so
              <codeph>READ UNCOMMITTED</codeph> is treated the same as <codeph>READ
              COMMITTED</codeph>.</pd>
          <pd><codeph>READ COMMITTED</codeph>, the default isolation level in Greenplum Database,
            guarantees that a statement can only see rows committed before it began. The same
            statement run twice in a transaction can produce different results if another
            concurrent transaction commits after the statement is run the first time. </pd>
          <pd>The <codeph>REPEATABLE READ</codeph> isolation level guarantees that a transaction can
            only see rows committed before it began. <codeph>REPEATABLE READ</codeph> is the
            strictest transaction isolation level Greenplum Database supports. Applications that use
            the <codeph>REPEATABLE READ</codeph> isolation level must be prepared to retry
            transactions due to serialization failures.</pd>
          <pd>The <codeph>SERIALIZABLE</codeph> transaction isolation level guarantees that all
            statements of the current transaction can only see rows committed before the first query
            or data-modification statement was run in this transaction. If a pattern of reads
            and writes among concurrent serializable transactions would create a situation which
            could not have occurred for any serial (one-at-a-time) execution of those transactions,
            one of the transactions will be rolled back with a
              <codeph>serialization_failure</codeph> error. Greenplum Database does not fully
            support <codeph>SERIALIZABLE</codeph> as defined by the standard, so if you specify
              <codeph>SERIALIZABLE</codeph>, Greenplum Database falls back to <codeph>REPEATABLE
              READ</codeph>. See <xref href="#topic1/section7" format="dita"/> for more information
            about transaction serializability in Greenplum Database.</pd>
        </plentry>
        <plentry>
          <pt>READ WRITE</pt>
          <pt>READ ONLY</pt>
          <pd>Determines whether the transaction is read/write or read-only. Read/write is the
            default. When a transaction is read-only, the following SQL commands are disallowed:
              <codeph>INSERT</codeph>, <codeph>UPDATE</codeph>, <codeph>DELETE</codeph>, and
              <codeph>COPY FROM</codeph> if the table they would write to is not a temporary table;
            all <codeph>CREATE</codeph>, <codeph>ALTER</codeph>, and <codeph>DROP</codeph> commands;
              <codeph>GRANT</codeph>, <codeph>REVOKE</codeph>, <codeph>TRUNCATE</codeph>; and
              <codeph>EXPLAIN ANALYZE</codeph> and <codeph>EXECUTE</codeph> if the command they
            would run is among those listed.</pd>
        </plentry>
        <plentry>
          <pt>[NOT] DEFERRABLE</pt>
          <pd>The <codeph>DEFERRABLE</codeph> transaction property has no effect in Greenplum
            Database because <codeph>SERIALIZABLE</codeph> transactions are not supported. If
              <codeph>DEFERRABLE</codeph> is specified and the transaction is also
              <codeph>SERIALIZABLE</codeph> and <codeph>READ ONLY</codeph>, the transaction may
            block when first acquiring its snapshot, after which it is able to run without the
            normal overhead of a <codeph>SERIALIZABLE</codeph> transaction and without any risk of
            contributing to or being cancelled by a serialization failure. This mode is well suited
            for long-running reports or backups.</pd>
        </plentry>
      </parml>
    </section>
    <section id="section5">
      <title>Notes</title>
      <p>If <codeph>SET TRANSACTION</codeph> is run without a prior <codeph>START
          TRANSACTION</codeph> or <codeph>BEGIN</codeph>, a warning is issued and the command has no
        effect. </p>
      <p>It is possible to dispense with <codeph>SET TRANSACTION</codeph> by instead specifying the
        desired transaction modes in <codeph>BEGIN</codeph> or <codeph>START TRANSACTION</codeph>. </p>
      <p>The session default transaction modes can also be set by setting the configuration
        parameters <xref href="../config_params/guc-list.xml#default_transaction_isolation"
            ><varname>default_transaction_isolation</varname></xref>, <xref
          href="../config_params/guc-list.xml#default_transaction_read_only"
            ><varname>default_transaction_read_only</varname></xref>, and <xref
          href="../config_params/guc-list.xml#default_transaction_deferrable"/>.</p>
    </section>
    <section id="section6">
      <title>Examples</title>
      <p>Set the transaction isolation level for the current transaction:</p>
      <codeblock>BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;</codeblock>
    </section>
    <section id="section7">
      <title>Compatibility</title>
      <p>Both commands are defined in the SQL standard. <codeph>SERIALIZABLE</codeph> is the default
        transaction isolation level in the standard. In Greenplum Database the default is
          <codeph>READ COMMITTED</codeph>. Due to lack of predicate locking, Greenplum Database does
        not fully support the <codeph>SERIALIZABLE</codeph> level, so it falls back to the
          <codeph>REPEATABLE READ</codeph> level when <codeph>SERIAL</codeph> is specified.
        Essentially, a predicate-locking system prevents phantom reads by restricting what is
        written, whereas a multi-version concurrency control model (MVCC) as used in Greenplum
        Database prevents them by restricting what is read. </p>
      <p>PostgreSQL provides a true serializable isolation level, called serializable snapshot
        isolation (SSI), which monitors concurrent transactions and rolls back transactions that
        could introduce serialization anomalies. Greenplum Database does not implement this
        isolation mode.</p>
      <p>In the SQL standard, there is one other transaction characteristic that can be set with
        these commands: the size of the diagnostics area. This concept is specific to embedded SQL,
        and therefore is not implemented in the Greenplum Database server. </p>
      <p>The <codeph>DEFERRABLE</codeph> transaction mode is a Greenplum Database language
        extension.</p>
      <p>The SQL standard requires commas between successive <varname>transaction_modes</varname>,
        but for historical reasons Greenplum Database allows the commas to be omitted.</p>
    </section>
    <section id="section8">
      <title>See Also</title>
      <p><codeph><xref href="BEGIN.xml#topic1" type="topic" format="dita"/></codeph>, <codeph><xref
            href="LOCK.xml#topic1" type="topic" format="dita"/></codeph></p>
    </section>
  </body>
</topic>
